#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *

from DataAccess.QueryBlp import *

from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *

def Month_Trading_Summary_Report(ref_date, portf_list = None, tick_list = None):

	start_date = date(ref_date.year, ref_date.month, 1)
	
	portf_av = query_post_AV(start_date, portf_list)
	portf_info = query_fund_info(portf_list)
	hld_before = query_hld(start_date - timedelta(days=1), portf_list, tick_list)
	hld_after = query_hld(ref_date, portf_list, tick_list)
	
	av = {} # av[portf_id] = [asset_value, curncy]
	for elem in portf_av:
		av[elem[0]] = [elem[1], '']
	for elem in portf_info:
		if av.has_key(elem[0]):
			av[elem[0]][1] = elem[2]
	
	hld_rcd = {} # hld_rcd[(ticker, portf_id)] = [quantity_before, expo_before, expo_after]
	for elem in hld_before:
		hld_rcd[(elem[0], elem[20])] = [elem[4], elem[14], 0.0]
	for elem in hld_after:
		if hld_rcd.has_key((elem[0], elem[20])):
			hld_rcd[(elem[0], elem[20])][2] = elem[14]
		else:
			hld_rcd[(elem[0], elem[20])] = [0.0, 0.0, elem[14]]
	
	trd = query_trade(start_date, ref_date, portf_list, tick_list)
	rcd = {} 
	for elem in trd:
		if not av.has_key(elem[11]):
			continue
		
		sec_tick = elem[3]
		sec_name = elem[4]
		sec_type = elem[5]
		direction = elem[6]
		quantity = elem[7]
		cost_price = elem[8]
		curncy = elem[9]
		portf_id = elem[11]
		portf_name = elem[12]
		amount = quantity * cost_price
		
		if rcd.has_key((sec_tick, sec_name, sec_type, curncy, direction, portf_id, portf_name)):
			rcd[(sec_tick, sec_name, sec_type, curncy, direction, portf_id, portf_name)][0] += quantity
			rcd[(sec_tick, sec_name, sec_type, curncy, direction, portf_id, portf_name)][1] += amount
		else:
			rcd[(sec_tick, sec_name, sec_type, curncy, direction, portf_id, portf_name)] = [quantity, amount]
	
	tplt_file = TPLT_PATH + 'Month Trading Summary Report.xls'
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(tplt_file)
	xSht = xBook.Worksheets('Report')
	
	xSht.Cells(3, 13).Value = start_date
	xSht.Cells(4, 13).Value = ref_date
	row = 8
	
	keys = rcd.keys()
	keys.sort()

	for k in keys:
		if k[2] == 'REPO':
			continue
			
		# if k[2] == 'PNOTE':
			# local_curncy = query_underlying_curncy(k[0])
			# local_cost = rcd[k][1]/rcd[k][0]*query_latest_fx(ref_date, k[3], local_curncy)
		# else:
			# local_curncy = k[3]
			# local_cost = rcd[k][1]/rcd[k][0]
		
		quantity_before = hld_rcd[(k[0], k[5])][0] if hld_rcd.has_key((k[0], k[5])) else 0
		trd_quantity = rcd[k][0] if k[4] == 'BUY' or k[4] == 'SHORT COVERING' else -rcd[k][0]
		expo_before = hld_rcd[(k[0], k[5])][1] if hld_rcd.has_key((k[0], k[5])) else 0
		expo_after = hld_rcd[(k[0], k[5])][2] if hld_rcd.has_key((k[0], k[5])) else 0
		local_cost = rcd[k][1]/rcd[k][0]
		local_curncy = k[3]
		
		xSht.Cells(row, 2).Value = k[0]
		xSht.Cells(row, 3).Value = k[1]
		xSht.Cells(row, 4).Value = k[2]
		xSht.Cells(row, 5).Value = k[4]
		xSht.Cells(row, 6).Value = k[6]
		xSht.Cells(row, 7).Value = expo_before
		xSht.Cells(row, 8).Value = expo_after
		xSht.Cells(row, 9).Value = expo_after - expo_before
		xSht.Cells(row, 10).Value = quantity_before
		xSht.Cells(row, 11).Value = trd_quantity
		xSht.Cells(row, 12).Value = local_cost
		xSht.Cells(row, 13).Value = local_curncy
		row += 1
	 
	rept_file = REPT_PATH + 'Month Trading Summary Report_' + ref_date.strftime("%Y%m") + '.xls'
	xBook.SaveAs(rept_file)
	xBook.Close()
	del xApp
	
	print 'info, month trading summary report completed,', ref_date.strftime("%Y%m")
	
	
def query_underlying_curncy(underlying_ticker):
	
	sql_tpl = Template('''select underlying_currency from pnote_info
							where underlying_ticker = '${TICK}' ''')
	sql_text = sql_tpl.substitute(TICK = underlying_ticker)
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	return r[0][0]








	 